import { link } from 'fs'
import mysql from 'mysql2'
// 数据库配置对象
const sqlConfig = {
    host: 'localhost',      // 连接数据库的地址
    user: 'root',           // 用户名
    password: 'qwer1234',   // 用户密码
    port: 3306,             // 端口号
    database: 'shop'        // 要使用的数据库名字
}
// 创建连接池
const pool = mysql.createPool(sqlConfig)
// 定义执行sql语句的方法
function exec(sql: string) {
    // 返回一个Promiss对象
    return new Promise((resolve, reject)=>{
        // 从连接池中获取一个连接
        pool.getConnection((err, connect) => {
            if (err) {
                reject(err)
            } else {
                // 执行SQL操作
                connect.query(sql, (error, result)=>{
                    if (error) { 
                        reject(error)
                    } else {
                        resolve({ result })
                    }
                })
            }
            // 释放连接
            connect?.release()
        })
    })
}
// 定义查询数据的方法 
// 可以指定查询的字段组
let queryData = (table: string, keys:string[], where: string, callback: (data: any)=>void) => {
    // 拼接完整的查询语句
    let keyString = ""
    keys.forEach((key)=>{
        keyString += key + ","
    })
    keyString = keyString.substring(0, keyString.length - 1)
    let sql = `select ${keyString} from ${table} where ${where}`
    exec(sql).then(result => {
        callback(result)
    }).catch((error)=>{
        console.log('sqlError:', error);
        callback(null)
    })
}

// 定义插入数据的方法
let insertData = (table: string, keys: string[], values: any[], callback: (data: any)=>void) => {
    // 拼接完整的SQL语句
    let keyString = "("
    keys.forEach((key)=>{
        keyString += key + ","
    })
    keyString = keyString.substring(0, keyString.length - 1)
    keyString += ')'
    let valueString = "("
    values.forEach((value)=>{
        if (typeof value === 'string') {
            valueString += "'" + value + "'" + ","
        } else {
            valueString +=  value + "," 
        }
    })
    valueString = valueString.substring(0, valueString.length - 1)
    valueString += ')'
    let sql = `insert into ${table} ${keyString} values ${valueString}`
    exec(sql).then(result => {
        callback(result)
    }).catch((error)=>{
        console.log('sqlError:', error);
        callback(null)
    })
}
// 更新方法
let updateData = (table: string, updateString: string, where: string) => {
    let sql = `update ${table} set ${updateString} where ${where}`
    exec(sql).then()
}

// 删除方法
let deleteData = (table: string, where: string) => {
    let sql = `delete from ${table} where ${where}`
    exec(sql).then()
}

// 联表查询方法
// mainTable: 主表名
// mainTableKeys: 主表要查询的字段
// othersKeys: 二维数组，每个元素为对应副表要查询的字段
// otherTableName: 要查询的副表名数组，与othersKeys顺序对应
// linkKeys: 主表数据中对应的副表的键名数组
// linkValues: 最终要组合进结果数据的属性名数组
let queryDataFrom = (mainTable:string, 
    mainTableKeys:string[], 
    othersKeys:string[][], 
    otherTableName:string[], 
    linkKeys: string[], 
    linkValues:string[], 
    where: string, 
    callback: (data: any)=>void) => {
    // 构建主表查询的字段字符串
    let keyString = ""
    mainTableKeys.forEach((key)=>{
        keyString += key + ","
    })
    keyString = keyString.substring(0, keyString.length - 1)
    // 构建副表查询的字符串
    let otherKeyString = ""
    let index = 0
    // 遍历所有副表
    otherTableName.forEach((table)=>{
        let otherString = ""
        // 副表要查询的字段
        let otherKeys = othersKeys[index]
        // 副表主键对应在主表中的字段名
        let linkKey = linkKeys[index]
        // 副表查询出的数据对应在结果对象中的属性名
        let linkValue = linkValues[index]
        // 遍历副表要查询的字段，构建语句
        otherKeys.forEach((otherK)=>{
            if (otherString.length > 0) {
                otherString += ','
            }
            otherString += `'${otherK}', ${table}.${otherK}`
        })
        if (otherKeyString.length > 0) {
            otherKeyString += ','
        }
        otherKeyString += `(select json_object(${otherString}) from ${table} ${table} where ${table}.id=o.${linkKey}) as ${linkValue}`
        index++
    })
    // 完整SQL语句拼接
    let sql = `select ${keyString}, ${otherKeyString} from ${mainTable} o where ${where}`
    exec(sql).then(result => {
        callback(result)
    }).catch((error)=>{
        console.log('sqlError:', error);
        callback(null)
    })
}

// 查询某个表中的数据个数
let dataCount = (tableName: string, callback:(data: any)=>void)=>{
    let sql = `SELECT COUNT(*) AS count FROM ${tableName};`
    exec(sql).then((result:any) => {
        callback(result.result[0].count)
    }).catch((error)=>{
        console.log('sqlError:', error);
        callback(null)
    })
}
// 查询最近1日内的数据条数
let dataCountWithinLastday = (tableName: string, dateName: string, callback:(data: any)=>void)=>{
    let sql = `SELECT DATE(${dateName}) AS date, COUNT(*) AS count
    FROM ${tableName}
    WHERE ${dateName} >= CURDATE() - INTERVAL 1 DAY
    GROUP BY DATE(${dateName})
    ORDER BY DATE(${dateName}) DESC;`
    exec(sql).then((result:any) => {
        callback(result.result[0].count)
    }).catch((error)=>{
        console.log('sqlError:', error);
        callback(null)
    })
}
// 查询最近一月内每日的数据，以日期维度聚合
let dataWithinMonth = (tableName: string, dateName: string, callback:(data: any)=>void) => {
    let sql = `SELECT DATE(${dateName}) AS date, COUNT(*) AS count
    FROM ${tableName}
    WHERE ${dateName} >= CURDATE() - INTERVAL 30 DAY
    GROUP BY DATE(${dateName})
    ORDER BY DATE(${dateName}) DESC;`
    exec(sql).then((result:any) => {
        callback(result.result)
    }).catch((error)=>{
        console.log('sqlError:', error);
        callback(null)
    })
}
// 进行数据求和，指定表名和要求和的字段名
let dataSum = (tableName: string, sumName: string, callback:(data: any)=>void) => {
    let sql = `SELECT SUM(${sumName}) AS total FROM ${tableName}`
    exec(sql).then((result:any) => {
        callback(result.result[0].total)
    }).catch((error)=>{
        console.log('sqlError:', error);
        callback(null)
    })
}
// 进行一个月内每日的数据求和，以日期为维度聚合
let dataSumWithinMonth = (tableName: string, dateName: string, sumName: string, callback:(data: any)=>void) => {
    let sql = `SELECT DATE(${dateName}) AS date, SUM(${sumName}) AS count
    FROM ${tableName}
    WHERE ${dateName} >= CURDATE() - INTERVAL 30 DAY
    GROUP BY DATE(${dateName})
    ORDER BY DATE(${dateName}) DESC;`
    exec(sql).then((result:any) => {
        callback(result.result)
    }).catch((error)=>{
        console.log('sqlError:', error);
        callback(null)
    })
}

// 导出工具方法
export default {queryData, insertData, updateData, deleteData, queryDataFrom, dataCount, dataWithinMonth, dataCountWithinLastday, dataSum, dataSumWithinMonth}